/*
 * sql.h
 *
 *  Created on: Apr 2, 2009
 *      Author: cauan
 */

#ifndef SQL_H_
#define SQL_H_

#include <sqlite3.h>

/***************\
 * SQL QUERIES *
\***************/

#define CREATE_TABLE_TAG	"CREATE TABLE IF NOT EXISTS TAG (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,NAME varchar NOT NULL UNIQUE COLLATE NOCASE);CREATE TRIGGER IF NOT EXISTS TAG_TAG_DELETE DELETE ON TAG BEGIN DELETE FROM TAG_TAG WHERE  TAG_ID = OLD.ID OR PARENT_ID = OLD.ID; END;"

#define CREATE_TABLE_TAG_TAG	"CREATE TABLE IF NOT EXISTS TAG_TAG (PARENT_ID integer NOT NULL, TAG_ID integer NOT NULL, FOREIGN KEY (TAG_ID) REFERENCES TAG(ID), FOREIGN KEY (PARENT_ID) REFERENCES TAG(ID));"

#define CREATE_ROOT		"INSERT OR IGNORE INTO TAG(NAME) VALUES(\"\");"

#define CREATE_TAG		"INSERT INTO TAG(NAME) VALUES(\"%s\"); INSERT INTO TAG_TAG (PARENT_ID, TAG_ID) VALUES( ( SELECT ID FROM TAG WHERE NAME = \"%s\" ), ( SELECT ID FROM TAG WHERE NAME = \"%s\")); "

#define SELECT_TAG		"SELECT ID FROM TAG WHERE NAME = \"%s\";"

#define LIST_TAG		"SELECT NAME FROM TAG WHERE ID IN ( SELECT TAG_ID FROM TAG_TAG WHERE PARENT_ID IN ( SELECT ID FROM TAG WHERE NAME = \"%s\" )); "

/*
#define CREATE_TABLE_TAG	"CREATE TABLE TAG (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,NAME varchar NOT NULL UNIQUE COLLATE NOCASE);CREATE TRIGGER TAG_TG_DELETE DELETE ON TAG BEGIN DELETE FROM TAG_TAG WHERE  TAG_ID = OLD.ID OR PARENT_ID = OLD.ID; DELETE FROM TAG_FILE WHERE  TAG_ID = OLD.ID; END;"
#define CREATE_TABLE_FILE	"CREATE TABLE FILE (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,NAME text NOT NULL UNIQUE COLLATE NOCASE, MD5 text); CREATE TRIGGER FILE_TG_DELETE DELETE ON FILE BEGIN DELETE FROM TAG_FILE WHERE  FILE_ID = OLD.ID; END;"
#define CREATE_TABLE_ALIAS	"CREATE TABLE ALIAS (ID integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, NAME text NOT NULL UNIQUE COLLATE NOCASE, TAG_ID integer NOT NULL,  FOREIGN KEY (TAG_ID) REFERENCES TAG(TAG_ID));"
#define CREATE_TABLE_TAG_TAG	"CREATE TABLE TAG_TAG (TAG_ID integer NOT NULL, PARENT_ID integer NOT NULL, FOREIGN KEY (TAG_ID) REFERENCES TAG(ID), FOREIGN KEY (PARENT_ID) REFERENCES TAG(ID));"
#define CREATE_TABLE_TAG_FILE	"CREATE TABLE TAG_FILE (TAG_ID integer NOT NULL, FILE_ID integer NOT NULL, FOREIGN KEY (TAG_ID) REFERENCES TAG(TAG_ID), FOREIGN KEY (FILE_ID) REFERENCES FILE(ID));"

//	TREE-FILE	-> CREATE VIEW VIEW_TREE_FILES AS SELECT TT.PARENT_ID, T.NAME AS PARENT_NAME, T2.NAME AS TAG_NAME, TT.TAG_ID, F.NAME AS FILE_NAME FROM TAG_TAG AS TT, TAG AS T, TAG AS T2, TAG_FILE AS TF, FILE AS F WHERE TT.PARENT_ID = T.ID AND TT.TAG_ID = T2.ID AND TF.TAG_ID = T2.ID AND TF.FILE_ID = F.ID
//	TREE-FILE*	-> CREATE VIEW VIEW_TREE_FILES AS SELECT T.NAME AS PARENT_NAME, T2.NAME AS TAG_NAME, F.NAME AS FILE_NAME FROM TAG_TAG AS TT, TAG AS T, TAG AS T2, TAG_FILE AS TF, FILE AS F WHERE TT.PARENT_ID = T.ID AND TT.TAG_ID = T2.ID AND TF.TAG_ID = T2.ID AND TF.FILE_ID = F.ID
//	TREE-TAG	-> CREATE VIEW VIEW_TREE_TAGS AS SELECT TT.PARENT_ID, T.NAME AS PARENT_NAME, T2.NAME, TT.TAG_ID  FROM TAG_TAG AS TT, TAG AS T, TAG AS T2 WHERE TT.PARENT_ID = T.ID AND TT.TAG_ID = T2.ID = T.ID AND TT.TAG_ID = T2.ID AND TF.TAG_ID = T2.ID AND TF.FILE_ID = F.ID
//	TREE-TAG*	-> CREATE VIEW VIEW_TREE_TAGS AS SELECT T.NAME AS PARENT_NAME, T2.NAME AS TAG_NAME  FROM TAG_TAG AS TT, TAG AS T, TAG AS T2 WHERE TT.PARENT_ID = T.ID AND TT.TAG_ID = T2.ID

#define CREATE_TAG			"INSERT INTO TAG(NAME) VALUES('%s');"
#define DELETE_TAG			"DELETE FROM TAG WHERE NAME = '%s'"
#define CREATE_FILE			"INSERT INTO FILE (NAME) VALUES ('%s');"
#define FILE_EXISTS			"SELECT NAME FROM FILE WHERE NAME = '%s';"
#define TAG_FILE 			"INSERT INTO TAG_FILE VALUES (( SELECT ID FROM TAG WHERE NAME = '%s'), (SELECT ID FROM FILE WHERE NAME = '%s'));"
#define UNTAG_FILE			"DELETE FROM TAG_FILE WHERE TAG_ID = (SELECT ID FROM TAG WHERE NAME = '%s') AND FILE_ID = (SELECT ID FROM FILE WHERE NAME = '%s');"
#define RENAME_TAG			"UPDATE TAG SET NAME = '%s' WHERE NAME = '%s';"
#define RENAME_FILE			"UPDATE FILE SET NAME = '%s' WHERE NAME = '%s';"
#define IS_TAGGED			"SELECT TAG_ID FROM TAG_FILE WHERE FILE_ID = (SELECT ID FROM FILE WHERE NAME = '%s') AND TAG_ID = (SELECT ID FROM TAG WHERE NAME = '%s');"
#define ALL_FILES_TAGGED	"SELECT NAME FROM FILE WHERE ID IN (SELECT FILE_ID FROM TAG_FILE WHERE TAG_ID = (SELECT ID FROM TAG WHERE NAME = '%s'));"
#define TAG_EXISTS			"SELECT NAME FROM TAG WHERE NAME = '%s';"
#define GET_ALL_TAGS		"SELECT NAME FROM TAG;"

#define GET_MIX_TAGS		"SELECT NAME FROM FILE WHERE ID IN (SELECT file_id FROM TAG_FILE WHERE tag_id = (SELECT id FROM tag WHERE name = 'tag1' ) );"

*/

/* GLOBAL Variables */
sqlite3 *db;

int sql_exec(sqlite3 *db, char *statement, char *callback);
void sql_exit(int s);


#endif /* SQL_H_ */
